﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using DAL;

namespace DAL
{
    public class WinQuestionEntityDal
    {

        private readonly SQLHelper sqlhelper;
        #region "实例化SQLHelper"
        /// <summary>
        /// SQLHelper实例化
        /// </summary>
        public WinQuestionEntityDal()
        {
            sqlhelper = new SQLHelper();
        }
        #endregion

        #region 根据试题类型获取题库中的试题--周洲--2015年11月21日
        /// <summary>
        /// 根据试题类型获取题库中的试题--周洲--2015年11月21日
        /// </summary>
        /// <param name="winquestion">传递考试类型</param>
        /// <returns>返回题目内容</returns>
        public DataTable LoadWindowsQuestion(WinQuestionEntity wininfo)
        {
            string cmdText = "select * from WinQuestionEntity where paperType =@paperType";

            SqlParameter[] paras = new SqlParameter[]{new SqlParameter ("@paperType",wininfo.paperType )
        };
            DataTable winQuestionDt = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.Text);
            return winQuestionDt;

        }
        #endregion

        #region"根据学号选择题型-韩梦甜-2015-11-20"
        /// <summary>
        /// 根据学号选择题型
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectPaperTypeByStudentID(StudentInfoEntity studentinfo)
        {
            string cmdText = "select paperType from WinQuestionRecordEntity where studentID=@studentinfo";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter ("@studentinfo",studentinfo.studentID )};
            DataTable dt = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region "加载试题信息到界面中，以试卷类型判断，查出题型-韩梦甜-2015-11-20"
        /// <summary>
        /// 加载试题信息到界面中，以试卷类型判断，查出题型
        /// </summary>
        /// <param name="studentinfo">传递学生号</param>
        /// <returns>返回题目的内容</returns>
        public DataTable LoadWindowsByFlag(WinQuestionEntity winquestion)
        {
            string cmdText = "select questionID,questionContent,questionFlag,fraction,paperType,correctAnswer from WinQuestionEntity where paperType=@paperType and questionFlag=@questionFlag";

            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@paperType",winquestion.paperType),
new SqlParameter ("@questionFlag",winquestion.questionFlag )
            };
            DataTable winQuestionDt = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.Text);
            return winQuestionDt;
        }
        #endregion

        #region 将答案和分数更新到数据库中-李芬-2015年12月26日10:44:15 
        /// <summary>
        /// 将答案和分数更新到数据库中
        /// </summary>
        /// <param name="list">StudentID,QuestionID,ExamAnswer,TimeStamp,fration</param>
        /// <param name="studentrecord">只需要一个studentID，为了查出学院ID</param>
        public void ReturnScore(List<WinQuestionRecordEntity> templist, WinQuestionRecordEntity studentrecord)
        {
            List<WinQuestionRecordEntity> list = new List<WinQuestionRecordEntity>();

            for (int i = 0; i < templist.Count; i++)
            {
                string questionid = templist[i].questionID.ToString();
                Boolean flag = false;
                for (int g = 0; g < list.Count; g++)
                {
                    if (list[g].questionID.ToString() == questionid)
                    {
                        flag = true;
                    }
                }
                if (flag == false)
                {
                    list.Add(templist[i]);
                }
            }
            String which = WhichWinRecored(studentrecord);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("update WinQuestionRecordEntity_" + which + " SET fraction = CASE questionID ");
            string Fation = string.Empty;
            string questionID = string.Empty;
            string studentIDs = string.Empty;
            string tamstamp = string.Empty;

            //根据学号，和时间戳选出这一段的内容，然后再根据QuestionID给每一个flag更新分数。
            #region 更新分数的拼接sql语句
            for (int i = 0; i < list.Count; i++)
            {
                questionID = list[i].questionID.ToString();
                //将分数传给变量
                Fation = list[i].fraction.ToString();
                //判分sql，拼接sql，用case，when语句
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + Fation + "'");
            }
            sbSql.Append(" end,"); //加“，” 
            #endregion

            #region 拼接考生答案更新到数据库中
            //拼接更新考生答案sql
            sbSql.Append("  examAnswer = CASE questionID");
            for (int i = 0; i < list.Count; i++)
            {
                questionID = list[i].questionID.ToString();
                //学生答案信息更新，when后边的是QuerstionID,
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + list[i].examAnswer + "'");
            }
            sbSql.Append(" end");   //不加逗号“，” 
            #endregion


            studentIDs += "'" + list[0].studentID.ToString() + "'";
            tamstamp += "'" + list[0].timeStamp.ToString() + "'";

            studentIDs.Remove(studentIDs.Length - 1, 1);
            sbSql.Append(" where studentID in (" + studentIDs + ") and timeStamp in (" + tamstamp + ")");
            DataTable dt = sqlhelper.ExecuteQuery(sbSql.ToString(), CommandType.Text);
        }
        #endregion
        #region 查找所有的wind的套卷 WinPaperType() 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的word的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable WinPaperType()
        {
            string sql = "select Distinct paperType from WinQuestionEntity";
            DataTable dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 给WordQutionEntity分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的word的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable WinPaperTypeGroupByPaperType()
        {
            string sql = "select * from WinQuestionEntity group by paperType";
            DataTable dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据PaperType查询分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        ///给WordQutionEntity分组
        /// </summary>
        /// <returns></returns>
        public DataTable WinPaperTypeGroupByPaperType(String PaperType)
        {
            string sql = "select * from WinQuestionEntity where paperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@PaperType",PaperType )
            };
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// <summary>
        /// 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertWinRecord(WinQuestionRecordEntity studentrecord)
        {
            String which = WhichWinRecored(studentrecord);
            string sql = "Insert into WinQuestsionRecordEntity_" + which + "(questionID,studentID,paperType,questionContent,correctAnswer) values(@QuestionID,@StudentID,@PaperType,@QuestionContent,@RightAnswer)";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.studentID ),
                new SqlParameter ("@PaperType",studentrecord.paperType ),
                new SqlParameter ("@QuestionContent",studentrecord.questionContent),
                new SqlParameter ("@RightAnswer",studentrecord.correctAnswer),
                new SqlParameter ("@QuestionID",studentrecord.questionID)
            };
            int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            return flag;
        }
        #endregion

        #region 根据学生的ID查询去重--邱慕夏  2015年11月23日14:28:57
        /// <summary>
        /// 根据学生的ID查询去重--邱慕夏--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public Boolean SelectWinRecord(WinQuestionRecordEntity studentrecord)
        {
            String which = WhichWinRecored(studentrecord);
            string sql = "Select * from  WinQuestionRecordEntity_" + which + " where studentID=@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.studentID ),
            };
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            if (dt.Rows.Count == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <summary>
        /// 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertWinRecordList(List<WinQuestionRecordEntity> studentrecordlist, StudentInfoEntity student)
        {
            int flag = 0;

            if (studentrecordlist.Count != 0)
            {
                String which = student.CollegeID;
                StringBuilder sbsql = new StringBuilder();
                sbsql.Append("Insert into WinQuestionRecordEntity_" + which + "(questionID,studentID,paperType,questionContent,correctAnswer,timeStamp) ");
                for (int i = 0; i < studentrecordlist.Count; i++)
                {
                    if (i != studentrecordlist.Count - 1)
                    {
                        sbsql = sbsql.Append("select '" + studentrecordlist[i].questionID + "','" + studentrecordlist[i].studentID + "','" + studentrecordlist[i].paperType + "','" + studentrecordlist[i].questionContent + "','" + studentrecordlist[i].correctAnswer + "','" + studentrecordlist[i].timeStamp + "' union all ");
                    }
                    else
                    {
                        sbsql = sbsql.Append("select '" + studentrecordlist[i].questionID + "','" + studentrecordlist[i].studentID + "','" + studentrecordlist[i].paperType + "','" + studentrecordlist[i].questionContent + "','" + studentrecordlist[i].correctAnswer + "','" + studentrecordlist[i].timeStamp + "'; ");
                    }

                }
                flag = sqlhelper.ExecuteNonQuery(sbsql.ToString(), CommandType.Text);

                return flag;
            }
            else
            {
                return 1;
            }
        }
        #endregion

        #region 看看向哪个数据库表中进行填写数据  邱慕夏  2015年11月23日14:21:20
        /// <summary>
        /// 看看向哪个数据库表中进行填写数据
        /// </summary>
        /// <param name="studentrecord"></param>
        /// <returns></returns>
        public String WhichWinRecored(WinQuestionRecordEntity studentrecord)
        {
            string sql = "select collegeId from StudentInfoEntity where studentID =@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.studentID),
            };
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            String which = dt.Rows[0][0].ToString();
            return which;
        }
        #endregion

        #region 查询该考生 是否有资格考试 -赵崇-2015年11月24日 16:39:57
        /// <summary>
        /// 查询该考生 是否有资格考试
        /// </summary>
        /// <param name="pEnStudent">学生</param>
        /// <returns>true表示可以进行考试，false表示 未进行配置 所以不能考试</returns>
        public bool GetIsCanExamByStudent(StudentInfoEntity pEnStudent)
        {

            string tableName = "";
            tableName = "WinQuestionRecordEntity_" + pEnStudent.CollegeID;

            //1,查询所有的选择题
            string sql = "select * from " + tableName + " where StudentID=@StudentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter ("@StudentID",pEnStudent.studentID )
            };
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);

            if (dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }
        #endregion

        #region 将新的Windows试题信息导入到数据库——陈新月——2018-5-5 17:19:39
        public int InsertNewWindowsQuestion(List<WinQuestionEntity> lstWinQuestion)
        {
            //1,查询所有的Windows题
            DataTable dt = new DataTable();
            string sqlselect = "select * from WinQuestionEntity";
            dt = sqlhelper.ExecuteQuery(sqlselect, CommandType.Text);
            int flag = 0;
            int i = 0;
            int j = 0;

            for (i = 0; i < lstWinQuestion.Count; i++)
            {
                string sql = "insert into WinQuestionEntity(questionID,questionTypeID,paperType,fraction,questionFlag,questionContent,correctAnswer,isUse) values(@questionID,@questionTypeID,@paperType,@fraction,@questionFlag,@questionContent,@correctAnswer,@isUse)";

                lstWinQuestion[i].questionID = (double)(dt.Rows.Count + i + 1);
                SqlParameter[] paras = new SqlParameter[]{
                    new SqlParameter("@questionID", lstWinQuestion[i].questionID),
                    new SqlParameter("@questionTypeID","2"),
                    new SqlParameter("@paperType", lstWinQuestion[i].paperType),
                    new SqlParameter("@fraction",lstWinQuestion[i].fraction),
                    new SqlParameter("@questionFlag",lstWinQuestion[i].questionFlag),
                    new SqlParameter("@questionContent", lstWinQuestion[i].questionContent),
                    new SqlParameter("@correctAnswer",lstWinQuestion[i].correctAnswer),
                    new SqlParameter("@isUse","On")
                };

                try
                {                   
                    //捕获错误数据标志  
                    flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
                    j++;
                }
                catch (Exception)
                {
                    //返回错误数据的标志
                    return j;
                }
            }
            return j;
        }
        #endregion

            #region 根据试卷类型查询分值和题型标志——陈新月——2018-04-08
        public DataTable InqueryWininfo(WinQuestionEntity winQuestion)
        {
            DataTable dt = new DataTable();
            string sql = "select fraction as 分值,questionflag as 题型标志 from WinQuestionEntity where paperType=@paperType";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@paperType",winQuestion.paperType)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据试卷类型更新配置好的分数——陈新月——2018-04-16
        public int UpdateWinInfo(List<WinQuestionEntity> listWin)
        {
            int UpdateWin = 0;
            for (int i = 0; i < listWin.Count; i++)
            {
                string sql = "update WinQuestionEntity set fraction=@fraction,questionFlag=@questionFlag where paperType=@paperType and questionFlag=@questionFlag";
                SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@paperType",listWin[i].paperType),
                new SqlParameter("@fraction",listWin[i].fraction),
                new SqlParameter("@questionFlag",listWin[i].questionFlag)
                };
                UpdateWin = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);

            }
            return UpdateWin;
        }
        #endregion
    }
}